{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge DataFrames in Pandas\n",
    "\n",
    "This notebooks shows how to read Pandas DataFrames from an Excel workbook and merge them based on specific rows. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import json\n",
    "from pandas import DataFrame\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "EXCEL_SOURCE_FILE = \"example_workbook.xlsx\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, the interface data from the Excel sheet is loaded to a new pandas DataFrame. To verify that the DataFrame contains some data, the `head()` function is used. It shows the first five entries within the DataFrame.\n",
    "\n",
    "Additional information about the `read_excel()` method is available in the [Pandas documentation](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hostname</th>\n",
       "      <th>interface_name</th>\n",
       "      <th>description</th>\n",
       "      <th>port_role</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/1</td>\n",
       "      <td>Router Port</td>\n",
       "      <td>Router</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hostname interface_name  description  port_role\n",
       "0  Switch_A           g0/1  Router Port     Router\n",
       "1  Switch_A           g0/2          NaN  Data_Port\n",
       "2  Switch_A           g0/3          NaN  Data_Port\n",
       "3  Switch_A           g0/4          NaN  Data_Port\n",
       "4  Switch_A           g0/5          NaN  Data_Port"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "interface_df = pd.read_excel(EXCEL_SOURCE_FILE, sheetname=\"interface\")\n",
    "interface_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The DataFrame should now be extended with information from the `port_role` sheet. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>Access VLAN ID</th>\n",
       "      <th>Voice VLAN ID</th>\n",
       "      <th>Trunk VLAN List</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Switch_to_Switch</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Router</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Printer_Port</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               name  Access VLAN ID  Voice VLAN ID Trunk VLAN List\n",
       "0         Data_Port              10            NaN             NaN\n",
       "1        Voice_Port              10           20.0             NaN\n",
       "2  Switch_to_Switch               1            NaN           1-100\n",
       "3            Router              80            NaN             NaN\n",
       "4      Printer_Port              11            NaN             NaN"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "port_role_df = pd.read_excel(EXCEL_SOURCE_FILE, sheetname=\"port_role\")\n",
    "port_role_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas provides a nice feature to merge data from two DataFrames by a specific column name. We have different key names in this example, therefore we need to specify the `left_on` and `right_on` parameter to identify the column that should be used for the merge operation.\n",
    "\n",
    "**Please Note:** The merge operation will create a new DataFrame that contains the values of the both DataFrames. The existing one won't be changed\n",
    "\n",
    "Further information about the merge function is available at [pandas documentation on merge](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hostname</th>\n",
       "      <th>interface_name</th>\n",
       "      <th>description</th>\n",
       "      <th>port_role</th>\n",
       "      <th>name</th>\n",
       "      <th>Access VLAN ID</th>\n",
       "      <th>Voice VLAN ID</th>\n",
       "      <th>Trunk VLAN List</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/1</td>\n",
       "      <td>Router Port</td>\n",
       "      <td>Router</td>\n",
       "      <td>Router</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hostname interface_name  description   port_role        name  \\\n",
       "0  Switch_A           g0/1  Router Port      Router      Router   \n",
       "1  Switch_A           g0/2          NaN   Data_Port   Data_Port   \n",
       "2  Switch_A           g0/3          NaN   Data_Port   Data_Port   \n",
       "3  Switch_A           g0/4          NaN   Data_Port   Data_Port   \n",
       "4  Switch_A           g0/5          NaN   Data_Port   Data_Port   \n",
       "5  Switch_A           g0/6          NaN  Voice_Port  Voice_Port   \n",
       "6  Switch_A           g0/7          NaN  Voice_Port  Voice_Port   \n",
       "7  Switch_A           g0/8          NaN  Voice_Port  Voice_Port   \n",
       "8  Switch_A           g0/9          NaN  Voice_Port  Voice_Port   \n",
       "9  Switch_A          g0/10          NaN  Voice_Port  Voice_Port   \n",
       "\n",
       "   Access VLAN ID  Voice VLAN ID Trunk VLAN List  \n",
       "0              80            NaN             NaN  \n",
       "1              10            NaN             NaN  \n",
       "2              10            NaN             NaN  \n",
       "3              10            NaN             NaN  \n",
       "4              10            NaN             NaN  \n",
       "5              10           20.0             NaN  \n",
       "6              10           20.0             NaN  \n",
       "7              10           20.0             NaN  \n",
       "8              10           20.0             NaN  \n",
       "9              10           20.0             NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_intf_df = pd.merge(interface_df, port_role_df, left_on=\"port_role\", right_on=\"name\")\n",
    "full_intf_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The new DataFrame contains now the general interface information and the information from the `port_role` sheet. The `port_role` and `name` contains the same values, therefore one can be removed from the DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hostname</th>\n",
       "      <th>interface_name</th>\n",
       "      <th>description</th>\n",
       "      <th>port_role</th>\n",
       "      <th>Access VLAN ID</th>\n",
       "      <th>Voice VLAN ID</th>\n",
       "      <th>Trunk VLAN List</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/1</td>\n",
       "      <td>Router Port</td>\n",
       "      <td>Router</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hostname interface_name  description  port_role  Access VLAN ID  \\\n",
       "0  Switch_A           g0/1  Router Port     Router              80   \n",
       "1  Switch_A           g0/2          NaN  Data_Port              10   \n",
       "2  Switch_A           g0/3          NaN  Data_Port              10   \n",
       "3  Switch_A           g0/4          NaN  Data_Port              10   \n",
       "4  Switch_A           g0/5          NaN  Data_Port              10   \n",
       "\n",
       "   Voice VLAN ID Trunk VLAN List  \n",
       "0            NaN             NaN  \n",
       "1            NaN             NaN  \n",
       "2            NaN             NaN  \n",
       "3            NaN             NaN  \n",
       "4            NaN             NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del full_intf_df[\"name\"]\n",
    "full_intf_df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the next steps, only the interface information for `Switch_A` are used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hostname</th>\n",
       "      <th>interface_name</th>\n",
       "      <th>description</th>\n",
       "      <th>port_role</th>\n",
       "      <th>Access VLAN ID</th>\n",
       "      <th>Voice VLAN ID</th>\n",
       "      <th>Trunk VLAN List</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/1</td>\n",
       "      <td>Router Port</td>\n",
       "      <td>Router</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Printer_Port</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/15</td>\n",
       "      <td>Trunk to Switch B</td>\n",
       "      <td>Switch_to_Switch</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Switch_A</td>\n",
       "      <td>g0/16</td>\n",
       "      <td>Trunk to Switch C</td>\n",
       "      <td>Switch_to_Switch</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1-100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    hostname interface_name        description         port_role  \\\n",
       "0   Switch_A           g0/1        Router Port            Router   \n",
       "1   Switch_A           g0/2                NaN         Data_Port   \n",
       "2   Switch_A           g0/3                NaN         Data_Port   \n",
       "3   Switch_A           g0/4                NaN         Data_Port   \n",
       "4   Switch_A           g0/5                NaN         Data_Port   \n",
       "5   Switch_A           g0/6                NaN        Voice_Port   \n",
       "6   Switch_A           g0/7                NaN        Voice_Port   \n",
       "7   Switch_A           g0/8                NaN        Voice_Port   \n",
       "8   Switch_A           g0/9                NaN        Voice_Port   \n",
       "9   Switch_A          g0/10                NaN        Voice_Port   \n",
       "22  Switch_A          g0/14                NaN      Printer_Port   \n",
       "25  Switch_A          g0/15  Trunk to Switch B  Switch_to_Switch   \n",
       "26  Switch_A          g0/16  Trunk to Switch C  Switch_to_Switch   \n",
       "\n",
       "    Access VLAN ID  Voice VLAN ID Trunk VLAN List  \n",
       "0               80            NaN             NaN  \n",
       "1               10            NaN             NaN  \n",
       "2               10            NaN             NaN  \n",
       "3               10            NaN             NaN  \n",
       "4               10            NaN             NaN  \n",
       "5               10           20.0             NaN  \n",
       "6               10           20.0             NaN  \n",
       "7               10           20.0             NaN  \n",
       "8               10           20.0             NaN  \n",
       "9               10           20.0             NaN  \n",
       "22              11            NaN             NaN  \n",
       "25               1            NaN           1-100  \n",
       "26               1            NaN           1-100  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "switch_a_intf_df = full_intf_df[full_intf_df.hostname == \"Switch_A\"]\n",
    "switch_a_intf_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The hostname is removed, because it's not required anymore."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>interface_name</th>\n",
       "      <th>description</th>\n",
       "      <th>port_role</th>\n",
       "      <th>Access VLAN ID</th>\n",
       "      <th>Voice VLAN ID</th>\n",
       "      <th>Trunk VLAN List</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>g0/1</td>\n",
       "      <td>Router Port</td>\n",
       "      <td>Router</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>g0/2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>g0/3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>g0/4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>g0/5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>g0/6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>g0/7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>g0/8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>g0/9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>g0/10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>g0/14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Printer_Port</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>g0/15</td>\n",
       "      <td>Trunk to Switch B</td>\n",
       "      <td>Switch_to_Switch</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>g0/16</td>\n",
       "      <td>Trunk to Switch C</td>\n",
       "      <td>Switch_to_Switch</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1-100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   interface_name        description         port_role  Access VLAN ID  \\\n",
       "0            g0/1        Router Port            Router              80   \n",
       "1            g0/2                NaN         Data_Port              10   \n",
       "2            g0/3                NaN         Data_Port              10   \n",
       "3            g0/4                NaN         Data_Port              10   \n",
       "4            g0/5                NaN         Data_Port              10   \n",
       "5            g0/6                NaN        Voice_Port              10   \n",
       "6            g0/7                NaN        Voice_Port              10   \n",
       "7            g0/8                NaN        Voice_Port              10   \n",
       "8            g0/9                NaN        Voice_Port              10   \n",
       "9           g0/10                NaN        Voice_Port              10   \n",
       "22          g0/14                NaN      Printer_Port              11   \n",
       "25          g0/15  Trunk to Switch B  Switch_to_Switch               1   \n",
       "26          g0/16  Trunk to Switch C  Switch_to_Switch               1   \n",
       "\n",
       "    Voice VLAN ID Trunk VLAN List  \n",
       "0             NaN             NaN  \n",
       "1             NaN             NaN  \n",
       "2             NaN             NaN  \n",
       "3             NaN             NaN  \n",
       "4             NaN             NaN  \n",
       "5            20.0             NaN  \n",
       "6            20.0             NaN  \n",
       "7            20.0             NaN  \n",
       "8            20.0             NaN  \n",
       "9            20.0             NaN  \n",
       "22            NaN             NaN  \n",
       "25            NaN           1-100  \n",
       "26            NaN           1-100  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del switch_a_intf_df[\"hostname\"]\n",
    "switch_a_intf_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The column names are directly used from the Excel sheet. To use them, for example in configuration templates, we need to clean the column names (remove the blanks and make them uppercase). The following code renames all columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INTERFACE_NAME</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>PORT_ROLE</th>\n",
       "      <th>ACCESS_VLAN_ID</th>\n",
       "      <th>VOICE_VLAN_ID</th>\n",
       "      <th>TRUNK_VLAN_LIST</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>g0/1</td>\n",
       "      <td>Router Port</td>\n",
       "      <td>Router</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>g0/2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>g0/3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>g0/4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>g0/5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>g0/6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>g0/7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>g0/8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>g0/9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>g0/10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>g0/14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Printer_Port</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>g0/15</td>\n",
       "      <td>Trunk to Switch B</td>\n",
       "      <td>Switch_to_Switch</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>g0/16</td>\n",
       "      <td>Trunk to Switch C</td>\n",
       "      <td>Switch_to_Switch</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1-100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   INTERFACE_NAME        DESCRIPTION         PORT_ROLE  ACCESS_VLAN_ID  \\\n",
       "0            g0/1        Router Port            Router              80   \n",
       "1            g0/2                NaN         Data_Port              10   \n",
       "2            g0/3                NaN         Data_Port              10   \n",
       "3            g0/4                NaN         Data_Port              10   \n",
       "4            g0/5                NaN         Data_Port              10   \n",
       "5            g0/6                NaN        Voice_Port              10   \n",
       "6            g0/7                NaN        Voice_Port              10   \n",
       "7            g0/8                NaN        Voice_Port              10   \n",
       "8            g0/9                NaN        Voice_Port              10   \n",
       "9           g0/10                NaN        Voice_Port              10   \n",
       "22          g0/14                NaN      Printer_Port              11   \n",
       "25          g0/15  Trunk to Switch B  Switch_to_Switch               1   \n",
       "26          g0/16  Trunk to Switch C  Switch_to_Switch               1   \n",
       "\n",
       "    VOICE_VLAN_ID TRUNK_VLAN_LIST  \n",
       "0             NaN             NaN  \n",
       "1             NaN             NaN  \n",
       "2             NaN             NaN  \n",
       "3             NaN             NaN  \n",
       "4             NaN             NaN  \n",
       "5            20.0             NaN  \n",
       "6            20.0             NaN  \n",
       "7            20.0             NaN  \n",
       "8            20.0             NaN  \n",
       "9            20.0             NaN  \n",
       "22            NaN             NaN  \n",
       "25            NaN           1-100  \n",
       "26            NaN           1-100  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we will use a list comprehension fot this\n",
    "column_replacements = dict(\n",
    "    zip(\n",
    "        switch_a_intf_df.columns,                                         # the current column names\n",
    "        [e.upper().replace(\" \", \"_\") for e in switch_a_intf_df.columns]   # the new column names\n",
    "    )\n",
    ")\n",
    "switch_a_intf_df = switch_a_intf_df.rename(columns=column_replacements)\n",
    "switch_a_intf_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `NaN` values are present if the DataFrame is converted to a string representation. To replace these values with an empty string, use the following `fillna()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INTERFACE_NAME</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>PORT_ROLE</th>\n",
       "      <th>ACCESS_VLAN_ID</th>\n",
       "      <th>VOICE_VLAN_ID</th>\n",
       "      <th>TRUNK_VLAN_LIST</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>g0/1</td>\n",
       "      <td>Router Port</td>\n",
       "      <td>Router</td>\n",
       "      <td>80</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>g0/2</td>\n",
       "      <td></td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>g0/3</td>\n",
       "      <td></td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>g0/4</td>\n",
       "      <td></td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>g0/5</td>\n",
       "      <td></td>\n",
       "      <td>Data_Port</td>\n",
       "      <td>10</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>g0/6</td>\n",
       "      <td></td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>g0/7</td>\n",
       "      <td></td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>g0/8</td>\n",
       "      <td></td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>g0/9</td>\n",
       "      <td></td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>g0/10</td>\n",
       "      <td></td>\n",
       "      <td>Voice_Port</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>g0/14</td>\n",
       "      <td></td>\n",
       "      <td>Printer_Port</td>\n",
       "      <td>11</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>g0/15</td>\n",
       "      <td>Trunk to Switch B</td>\n",
       "      <td>Switch_to_Switch</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>1-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>g0/16</td>\n",
       "      <td>Trunk to Switch C</td>\n",
       "      <td>Switch_to_Switch</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>1-100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   INTERFACE_NAME        DESCRIPTION         PORT_ROLE  ACCESS_VLAN_ID  \\\n",
       "0            g0/1        Router Port            Router              80   \n",
       "1            g0/2                            Data_Port              10   \n",
       "2            g0/3                            Data_Port              10   \n",
       "3            g0/4                            Data_Port              10   \n",
       "4            g0/5                            Data_Port              10   \n",
       "5            g0/6                           Voice_Port              10   \n",
       "6            g0/7                           Voice_Port              10   \n",
       "7            g0/8                           Voice_Port              10   \n",
       "8            g0/9                           Voice_Port              10   \n",
       "9           g0/10                           Voice_Port              10   \n",
       "22          g0/14                         Printer_Port              11   \n",
       "25          g0/15  Trunk to Switch B  Switch_to_Switch               1   \n",
       "26          g0/16  Trunk to Switch C  Switch_to_Switch               1   \n",
       "\n",
       "   VOICE_VLAN_ID TRUNK_VLAN_LIST  \n",
       "0                                 \n",
       "1                                 \n",
       "2                                 \n",
       "3                                 \n",
       "4                                 \n",
       "5             20                  \n",
       "6             20                  \n",
       "7             20                  \n",
       "8             20                  \n",
       "9             20                  \n",
       "22                                \n",
       "25                         1-100  \n",
       "26                         1-100  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_list = switch_a_intf_df.fillna(\"\")\n",
    "clean_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, every row is converted to a `dictionary` and added to another dictionary that can be used, e.g. with Jinja2 to generate a configurations. I wrote another article about the [configuration generation with python and Jinja2](https://codingnetworker.com/2015/09/configuration-generator-with-python-and-jinja2/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "    \"HOSTNAME\": \"Switch A\",\n",
      "    \"PORTS\": [\n",
      "        {\n",
      "            \"DESCRIPTION\": \"Router Port\",\n",
      "            \"ACCESS_VLAN_ID\": 80,\n",
      "            \"INTERFACE_NAME\": \"g0/1\",\n",
      "            \"TRUNK_VLAN_LIST\": \"\",\n",
      "            \"VOICE_VLAN_ID\": \"\",\n",
      "            \"PORT_ROLE\": \"Router\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"\",\n",
      "            \"ACCESS_VLAN_ID\": 10,\n",
      "            \"INTERFACE_NAME\": \"g0/2\",\n",
      "            \"TRUNK_VLAN_LIST\": \"\",\n",
      "            \"VOICE_VLAN_ID\": \"\",\n",
      "            \"PORT_ROLE\": \"Data_Port\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"\",\n",
      "            \"ACCESS_VLAN_ID\": 10,\n",
      "            \"INTERFACE_NAME\": \"g0/3\",\n",
      "            \"TRUNK_VLAN_LIST\": \"\",\n",
      "            \"VOICE_VLAN_ID\": \"\",\n",
      "            \"PORT_ROLE\": \"Data_Port\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"\",\n",
      "            \"ACCESS_VLAN_ID\": 10,\n",
      "            \"INTERFACE_NAME\": \"g0/4\",\n",
      "            \"TRUNK_VLAN_LIST\": \"\",\n",
      "            \"VOICE_VLAN_ID\": \"\",\n",
      "            \"PORT_ROLE\": \"Data_Port\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"\",\n",
      "            \"ACCESS_VLAN_ID\": 10,\n",
      "            \"INTERFACE_NAME\": \"g0/5\",\n",
      "            \"TRUNK_VLAN_LIST\": \"\",\n",
      "            \"VOICE_VLAN_ID\": \"\",\n",
      "            \"PORT_ROLE\": \"Data_Port\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"\",\n",
      "            \"ACCESS_VLAN_ID\": 10,\n",
      "            \"INTERFACE_NAME\": \"g0/6\",\n",
      "            \"TRUNK_VLAN_LIST\": \"\",\n",
      "            \"VOICE_VLAN_ID\": 20.0,\n",
      "            \"PORT_ROLE\": \"Voice_Port\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"\",\n",
      "            \"ACCESS_VLAN_ID\": 10,\n",
      "            \"INTERFACE_NAME\": \"g0/7\",\n",
      "            \"TRUNK_VLAN_LIST\": \"\",\n",
      "            \"VOICE_VLAN_ID\": 20.0,\n",
      "            \"PORT_ROLE\": \"Voice_Port\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"\",\n",
      "            \"ACCESS_VLAN_ID\": 10,\n",
      "            \"INTERFACE_NAME\": \"g0/8\",\n",
      "            \"TRUNK_VLAN_LIST\": \"\",\n",
      "            \"VOICE_VLAN_ID\": 20.0,\n",
      "            \"PORT_ROLE\": \"Voice_Port\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"\",\n",
      "            \"ACCESS_VLAN_ID\": 10,\n",
      "            \"INTERFACE_NAME\": \"g0/9\",\n",
      "            \"TRUNK_VLAN_LIST\": \"\",\n",
      "            \"VOICE_VLAN_ID\": 20.0,\n",
      "            \"PORT_ROLE\": \"Voice_Port\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"\",\n",
      "            \"ACCESS_VLAN_ID\": 10,\n",
      "            \"INTERFACE_NAME\": \"g0/10\",\n",
      "            \"TRUNK_VLAN_LIST\": \"\",\n",
      "            \"VOICE_VLAN_ID\": 20.0,\n",
      "            \"PORT_ROLE\": \"Voice_Port\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"\",\n",
      "            \"ACCESS_VLAN_ID\": 11,\n",
      "            \"INTERFACE_NAME\": \"g0/14\",\n",
      "            \"TRUNK_VLAN_LIST\": \"\",\n",
      "            \"VOICE_VLAN_ID\": \"\",\n",
      "            \"PORT_ROLE\": \"Printer_Port\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"Trunk to Switch B\",\n",
      "            \"ACCESS_VLAN_ID\": 1,\n",
      "            \"INTERFACE_NAME\": \"g0/15\",\n",
      "            \"TRUNK_VLAN_LIST\": \"1-100\",\n",
      "            \"VOICE_VLAN_ID\": \"\",\n",
      "            \"PORT_ROLE\": \"Switch_to_Switch\"\n",
      "        },\n",
      "        {\n",
      "            \"DESCRIPTION\": \"Trunk to Switch C\",\n",
      "            \"ACCESS_VLAN_ID\": 1,\n",
      "            \"INTERFACE_NAME\": \"g0/16\",\n",
      "            \"TRUNK_VLAN_LIST\": \"1-100\",\n",
      "            \"VOICE_VLAN_ID\": \"\",\n",
      "            \"PORT_ROLE\": \"Switch_to_Switch\"\n",
      "        }\n",
      "    ]\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "result = {\n",
    "    \"HOSTNAME\": \"Switch A\",  # we only have Switch A in this case\n",
    "    \"PORTS\": []\n",
    "}\n",
    "for index, row in clean_list.iterrows():\n",
    "    result[\"PORTS\"].append(row.to_dict())\n",
    "\n",
    "print(json.dumps(result, indent=4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
